package com.ph.irp.base.usermanage.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.usermanage.dao.PBasePurviewUserroleDAO;
import com.ph.util.DBUtil;

public class PBasePurviewUserroleDAOImpl implements PBasePurviewUserroleDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void addUserRole(int userID, int roleID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("insert into p_base_purview_userrole(RoleID,UserID)");
			sb.append(" values(");
			sb.append(roleID);
			sb.append(",").append(userID);
			sb.append(")");

			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public List<Integer> getUserRoleByRoleId(String roleId) throws Throwable {

		List<Integer> resList = new ArrayList<Integer>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.UserID userid ");
			sb.append("from p_base_purview_userrole u ");
			sb.append("where u.RoleID =").append(Integer.parseInt(roleId));
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				resList.add(rs.getInt("userid"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	public List<Integer> getUserRoleByUserId(String userId) throws Throwable {
		List<Integer> resList = new ArrayList<Integer>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.RoleID roleid ");
			sb.append("from p_base_purview_userrole u ");
			sb.append("where u.UserID =").append(Integer.parseInt(userId));
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				resList.add(rs.getInt("roleid"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public String getUserRoleNameByRoleId(int roleId) throws Throwable {
		String res = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select u.RoleName roleName ");
			sb.append("from p_base_purview_role u ");
			sb.append("where u.RoleID =").append(roleId);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getString("roleName");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return res;
	}

	public void deleteUserRole(int roleId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("delete from p_base_purview_userrole ");
			sb.append("where roleId=").append(roleId);

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public void deleteUserRoleByRoleIDs(String deleteIds) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from p_base_purview_userrole ");
			sb.append(" where RoleID in (").append(deleteIds).append(")");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, null);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public void deleteUserRoleByUserIDs(String deleteIds) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from p_base_purview_userrole ");
			sb.append(" where UserID in (").append(deleteIds).append(")");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, null);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}
}
